﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Net;
using System.Net.Sockets;

namespace WpfApplication1
{
    class Login
    {
        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["Groups"].ToString();

        string sql = "Select * FROM [User] Where UserName = @UserName";
        
        private string userName;
        private string passWord;

        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }
        public string Password
        {
            get { return passWord; }
            set { passWord = value; }
        }

       

        
        public string LoginUser()
        {

            
            string a1 = "Invalid User";
            string a2 = "Incorrect Password";            
            string a3 = "Success";
            
            DataTable dataTable1 = new DataTable();
            

            using (SqlConnection cn = new SqlConnection(connection))
            {

                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {

                    cmd.Parameters.AddWithValue("@UserName", userName);
                    cmd.Parameters.AddWithValue("@Password", passWord);
                    cn.Open();

                    

                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dataTable1);
                    cn.Close();                   
                    da.Dispose();

                    if (dataTable1.Rows.Count == 0)
                    {

                        return a1;
                        
                    }
                                       

                    else if (Password != dataTable1.Rows[0]["Password"].ToString())
                    {
                        return a2;
                    }

                    else
                    {
                        return a3;
                    }

                        

                    
                }
            }
        }
        

            
        
        
        //public void ResetPassword(string userName1, string passWord1)
        //{
            
        //    string connectionString = "Server=t-sql;DataBase=ConfigurationManager;Trusted_Connection=Yes;";
        //    string sql = "UPDATE [User] SET UserName = @UserName, Password = @Password WHERE UserName = @UserName AND Password = @Password";


        //    using (SqlConnection cn = new SqlConnection(connectionString))
        //    {

        //        using (SqlCommand cmd = new SqlCommand(sql, cn))
        //        {

        //            cmd.Parameters.AddWithValue("@UserName", userName);
        //            cmd.Parameters.AddWithValue("@Password", passWord);
        //            cn.Open();
        //            cmd.ExecuteNonQuery();
                    
        //        }
        //    }
        //}
        
                
                public DataTable DisplaySession(string username)
                {
                    DataTable dataTable = new DataTable();
                    foreach (DataRow dr in dataTable.Rows)
                    {
                                                
                        dataTable.Columns.Add("FirstName", typeof(string));
                        dataTable.Columns.Add("LastName", typeof(string));
                        dataTable.Columns.Add("GroupName", typeof(string));                        
                        dataTable.Columns.Add("AttribName", typeof(string));
                        dataTable.Columns.Add("Value", typeof(string));
                        dataTable.Columns.Add("GroupPriority", typeof(int));
                    }



                    string connection = System.Configuration.ConfigurationManager.ConnectionStrings["Groups"].ToString();
                    string sql = @"With TopGroups (EmpID, AttribName, AttribID, GroupPriority)

                    as

                        (

                    select [User].EmpID, Attrib.AttribName, Attrib.AttribID,max(GroupPriority) from

                    [Group], [User], UserGroup, GroupAttrib, Attrib

                    Where [User].EmpID = UserGroup.EmpID

                    AND UserGroup.GroupID = [Group].GroupID

                    And GroupAttrib.GroupID = [Group].GroupID

                    AND GroupAttrib.AttribID = Attrib.AttribID
 

                    Group by Attrib.AttribID, attrib.AttribName, [User].EmpID

                     )

                    select [User].FirstName, [User].LastName, [Group].GroupName,

                    Attrib.AttribName, GroupAttrib.Value,

                     TopGroups.GroupPriority

                    from [TopGroups],[Group], [User], UserGroup, GroupAttrib, Attrib



                    Where [User].UserName = @UserName

                    AND [User].EmpID = UserGroup.EmpID

                    AND [User].EmpID = TopGroups.EmpID

                     AND UserGroup.GroupID = [Group].GroupID

                    And GroupAttrib.GroupID = [Group].GroupID

                    AND GroupAttrib.AttribID = Attrib.AttribID

                    AND [Group].GroupPriority = TopGroups.GroupPriority

                    Group by Attrib.AttribName, [User].FirstName, [User].LastName,

                    TopGroups.GroupPriority, [Group].GroupName, GroupAttrib.Value";

                


            using (SqlConnection cn = new SqlConnection(connection))
            {
                                    
                
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                     
                    cmd.Parameters.AddWithValue("@UserName", userName);
                    cn.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dataTable);                    
                    return dataTable;
                    

                }
            }
        }

    }
}